﻿using Anke.SHManage.Model;
using Anke.SHManage.Model.FixedAssetsModel;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL.FixedAssetsDAL
{
    /// <summary>
    /// 固定资产模块 数据字典 DAL层
    /// plq
    /// </summary>
    public class FixedAssetsDictionaryDAL
    {
        /// <summary>
        /// 获取所有固定资产类型list---不筛选已禁用
        /// </summary>
        /// <returns></returns>
        public List<Model.TDictionary> GetAllAssetsTypeList()
        {
            List<TDictionary> list = new List<TDictionary>();
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(@"select ID,Name,TypeID,SN,IsActive,ParentID from TDictionary where 1 = 1 ");
            stringBuilder.Append(@"
                                  and TypeID='AssetsType' "); //固定资产类型
            stringBuilder.Append(@"
                                   order by SN");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, stringBuilder.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        TDictionary info = new TDictionary();
                        info.ID = dr["ID"].ToString();
                        info.Name = dr["Name"].ToString();
                        info.TypeID = dr["TypeID"].ToString();
                        info.SN = int.Parse(dr["SN"].ToString());
                        info.IsActive = Convert.ToBoolean(dr["IsActive"].ToString());
                        info.ParentID = dr["ParentID"].ToString();
                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetAssetsTypeList", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取固定资产类型树---未禁用
        /// </summary>
        /// <returns></returns>
        public List<TDictionary> GetActiveAssetsTypeList()
        {
            List<TDictionary> list = new List<TDictionary>();
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append(@"select ID,Name,TypeID,SN,IsActive,ParentID from TDictionary where 1 = 1 ");
            stringBuilder.Append(@"
                                  and IsActive=1 "); //固定资产类型
            stringBuilder.Append(@"
                                  and TypeID='AssetsType' "); //固定资产类型
            stringBuilder.Append(@"
                                   order by SN");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, stringBuilder.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        TDictionary info = new TDictionary();
                        info.ID = dr["ID"].ToString();
                        info.Name = dr["Name"].ToString();
                        info.TypeID = dr["TypeID"].ToString();
                        info.SN = int.Parse(dr["SN"].ToString());
                        info.IsActive = Convert.ToBoolean(dr["IsActive"].ToString());
                        info.ParentID = dr["ParentID"].ToString();
                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetActiveAssetsTypeList", ex.Message);
                return null;
            }
        }


        /// <summary>
        /// 获取所有资产流水
        /// </summary>
        /// <returns></returns>
        public object GetAssetList()
        {
            List<FIXED_MatetialInfo> list = new List<FIXED_MatetialInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select fm.编码,fm.固定资产编码,fm.类型编码,fm.资产名称,fm.主机序列号,fm.品牌,fm.型号,fm.规格 ");
            sb.Append(" ,fm.单位,fm.数量,fm.发票号码,fm.单价,fm.购建日期,fm.入库日期,fm.当前仓库编码,fm.当前存放点 ");
            sb.Append(" ,fm.当前车牌号码,fm.资产管理部门,fm.资产管理人,fm.备注,fm.是否报废 ");
            sb.Append(" ,类型=td.Name ");
            sb.Append(" ,当前仓库=fs.仓库名称 ");
            sb.Append(@"   
                from FIXED_Matetial fm ");
            sb.Append(@"   
                left join TDictionary td on td.ID=fm.类型编码");
            sb.Append(@"   
                left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码");
            sb.Append(@"
                where 1=1 ");
            sb.Append(@"
                order by 入库日期,购建日期,资产名称 ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_MatetialInfo info = new FIXED_MatetialInfo();
                        info.BianMa = new Guid(dr["编码"].ToString());
                        info.GuDingZiChanBianMa = dr["固定资产编码"].ToString();
                        info.LeiXingBianMa = dr["类型编码"].ToString();
                        info.LeiXing = dr["类型"].ToString();
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();
                        info.PinPai = dr["品牌"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.DanWei = dr["单位"].ToString();
                        info.ShuLiang = dr["数量"].ToString();
                        info.FaPiaoHaoMa = dr["发票号码"].ToString();
                        info.DanJia = Convert.ToDouble(dr["单价"].ToString());
                        info.GouJianRiQi = Convert.ToDateTime(dr["购建日期"].ToString());
                        info.RuKuRiQi = Convert.ToDateTime(dr["入库日期"].ToString());
                        info.DangQianCangKuBianMa = Convert.ToInt32(dr["当前仓库编码"].ToString());
                        info.DangQianCangKu = dr["当前仓库"].ToString();
                        info.DangQianCunFangDian = dr["当前存放点"].ToString();
                        info.DangQianChePaiHaoMa = dr["当前车牌号码"].ToString();
                        info.ZiChanGuanLiBuMen = dr["资产管理部门"].ToString();
                        info.ZiChanGuanLiRen = dr["资产管理人"].ToString();
                        info.BeiZhu = dr["备注"].ToString();
                        info.ShiFouBaoFei = Convert.ToBoolean(dr["是否报废"]);

                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetAssetList", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据分类取资产流水
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public object GetAssetListByType(string typeId)
        {
            List<FIXED_MatetialInfo> list = new List<FIXED_MatetialInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select fm.编码,fm.固定资产编码,fm.类型编码,fm.资产名称,fm.主机序列号,fm.品牌,fm.型号,fm.规格 ");
            sb.Append(" ,fm.单位,fm.数量,fm.发票号码,fm.单价,fm.购建日期,fm.入库日期,fm.当前仓库编码,fm.当前存放点 ");
            sb.Append(" ,fm.当前车牌号码,fm.资产管理部门,fm.资产管理人,fm.备注,fm.是否报废 ");
            sb.Append(" ,类型=td.Name ");
            sb.Append(" ,当前仓库=fs.仓库名称 ");
            sb.Append(@"   
                from FIXED_Matetial fm ");
            sb.Append(@"   
                left join TDictionary td on td.ID=fm.类型编码");
            sb.Append(@"   
                left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码");
            sb.Append(@"
                where 1=1 ");

            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", typeId, sb);
            if ((typeId.Length > 0) && (typeId != null) && (typeId != "-1") && (typeId != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(typeId); //分类编码list
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("类型编码", s1, sb);
            }
            sb.Append(@"
                order by 入库日期,购建日期,资产名称 ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_MatetialInfo info = new FIXED_MatetialInfo();
                        info.BianMa = new Guid(dr["编码"].ToString());
                        info.GuDingZiChanBianMa = dr["固定资产编码"].ToString();
                        info.LeiXingBianMa = dr["类型编码"].ToString();
                        info.LeiXing = dr["类型"].ToString();
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();
                        info.PinPai = dr["品牌"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.DanWei = dr["单位"].ToString();
                        info.ShuLiang = dr["数量"].ToString();
                        info.FaPiaoHaoMa = dr["发票号码"].ToString();
                        info.DanJia = Convert.ToDouble(dr["单价"].ToString());
                        info.GouJianRiQi = Convert.ToDateTime(dr["购建日期"].ToString());
                        info.RuKuRiQi = Convert.ToDateTime(dr["入库日期"].ToString());
                        info.DangQianCangKuBianMa = Convert.ToInt32(dr["当前仓库编码"].ToString());
                        info.DangQianCangKu = dr["当前仓库"].ToString();
                        info.DangQianCunFangDian = dr["当前存放点"].ToString();
                        info.DangQianChePaiHaoMa = dr["当前车牌号码"].ToString();
                        info.ZiChanGuanLiBuMen = dr["资产管理部门"].ToString();
                        info.ZiChanGuanLiRen = dr["资产管理人"].ToString();
                        info.BeiZhu = dr["备注"].ToString();
                        info.ShiFouBaoFei = Convert.ToBoolean(dr["是否报废"]);

                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetAssetListByType", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 递归查询该分类ID下的所有list（分类及后代分类）-适用于树结构数据
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        private List<string> GetTDicStrListByID(string typeId)
        {
            List<string> list = new List<string>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"with dic 
                            as( select * 
                                from TDictionary 
                                where ID in ('" + typeId + @"')
                                union all 
                                select TDictionary.* 
                                from dic , TDictionary 
                                where dic.id = TDictionary.ParentID
                            )
                          select * from dic");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        list.Add(dr["ID"].ToString());
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetTDicStrListByID", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 取所有资产流水--未报废
        /// 2019-10-21 plq
        /// </summary>
        /// <returns></returns>
        public object GetAssetListNotScrap()
        {
            List<FIXED_MatetialInfo> list = new List<FIXED_MatetialInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select fm.编码,fm.固定资产编码,fm.类型编码,fm.资产名称,fm.主机序列号,fm.品牌,fm.型号,fm.规格 ");
            sb.Append(" ,fm.单位,fm.数量,fm.发票号码,fm.单价,fm.购建日期,fm.入库日期,fm.当前仓库编码,fm.当前存放点 ");
            sb.Append(" ,fm.当前车牌号码,fm.资产管理部门,fm.资产管理人,fm.备注,fm.是否报废 ");
            sb.Append(" ,类型=td.Name ");
            sb.Append(" ,当前仓库=fs.仓库名称 ");
            sb.Append(@"   
                from FIXED_Matetial fm ");
            sb.Append(@"   
                left join TDictionary td on td.ID=fm.类型编码");
            sb.Append(@"   
                left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码");
            sb.Append(@"
                where 1=1 ");
            sb.Append(@"
                and 是否报废=0 ");
            sb.Append(@"
                order by 入库日期,购建日期,资产名称 ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_MatetialInfo info = new FIXED_MatetialInfo();
                        info.BianMa = new Guid(dr["编码"].ToString());
                        info.GuDingZiChanBianMa = dr["固定资产编码"].ToString();
                        info.LeiXingBianMa = dr["类型编码"].ToString();
                        info.LeiXing = dr["类型"].ToString();
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();
                        info.PinPai = dr["品牌"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.DanWei = dr["单位"].ToString();
                        info.ShuLiang = dr["数量"].ToString();
                        info.FaPiaoHaoMa = dr["发票号码"].ToString();
                        info.DanJia = Convert.ToDouble(dr["单价"].ToString());
                        info.GouJianRiQi = Convert.ToDateTime(dr["购建日期"].ToString());
                        info.RuKuRiQi = Convert.ToDateTime(dr["入库日期"].ToString());
                        info.DangQianCangKuBianMa = Convert.ToInt32(dr["当前仓库编码"].ToString());
                        info.DangQianCangKu = dr["当前仓库"].ToString();
                        info.DangQianCunFangDian = dr["当前存放点"].ToString();
                        info.DangQianChePaiHaoMa = dr["当前车牌号码"].ToString();
                        info.ZiChanGuanLiBuMen = dr["资产管理部门"].ToString();
                        info.ZiChanGuanLiRen = dr["资产管理人"].ToString();
                        info.BeiZhu = dr["备注"].ToString();
                        info.ShiFouBaoFei = Convert.ToBoolean(dr["是否报废"]);

                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetAssetListNotScrap", ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据分类取资产流水---未报废
        ///  2019-10-21 plq
        /// </summary>
        /// <returns></returns>
        public object GetAssetListNotScrapByType(string typeId)
        {
            List<FIXED_MatetialInfo> list = new List<FIXED_MatetialInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(" select fm.编码,fm.固定资产编码,fm.类型编码,fm.资产名称,fm.主机序列号,fm.品牌,fm.型号,fm.规格 ");
            sb.Append(" ,fm.单位,fm.数量,fm.发票号码,fm.单价,fm.购建日期,fm.入库日期,fm.当前仓库编码,fm.当前存放点 ");
            sb.Append(" ,fm.当前车牌号码,fm.资产管理部门,fm.资产管理人,fm.备注,fm.是否报废 ");
            sb.Append(" ,类型=td.Name ");
            sb.Append(" ,当前仓库=fs.仓库名称 ");
            sb.Append(@"   
                from FIXED_Matetial fm ");
            sb.Append(@"   
                left join TDictionary td on td.ID=fm.类型编码");
            sb.Append(@"   
                left join FIXED_Storage fs on fs.仓库编码=fm.当前仓库编码");
            sb.Append(@"
                where 1=1 ");
            sb.Append(@"
                and 是否报废=0 ");

            //分类筛选
            //WhereClauseUtility.AddStringEqual("类型编码", typeId, sb);
            if ((typeId.Length > 0) && (typeId != null) && (typeId != "-1") && (typeId != "--请选择--"))
            {
                List<string> typeIDList = GetTDicStrListByID(typeId); //分类编码list
                string s1 = "'" + string.Join("','", typeIDList.ToArray()) + "'";
                WhereClauseUtility.AddInSelectQuery("类型编码", s1, sb);
            }
            sb.Append(@"
                order by 入库日期,购建日期,资产名称 ");
            try
            {
                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        FIXED_MatetialInfo info = new FIXED_MatetialInfo();
                        info.BianMa = new Guid(dr["编码"].ToString());
                        info.GuDingZiChanBianMa = dr["固定资产编码"].ToString();
                        info.LeiXingBianMa = dr["类型编码"].ToString();
                        info.LeiXing = dr["类型"].ToString();
                        info.ZiChanMingCheng = dr["资产名称"].ToString();
                        info.ZhuJiXuLieHao = dr["主机序列号"].ToString();
                        info.PinPai = dr["品牌"].ToString();
                        info.XingHao = dr["型号"].ToString();
                        info.GuiGe = dr["规格"].ToString();
                        info.DanWei = dr["单位"].ToString();
                        info.ShuLiang = dr["数量"].ToString();
                        info.FaPiaoHaoMa = dr["发票号码"].ToString();
                        info.DanJia = Convert.ToDouble(dr["单价"].ToString());
                        info.GouJianRiQi = Convert.ToDateTime(dr["购建日期"].ToString());
                        info.RuKuRiQi = Convert.ToDateTime(dr["入库日期"].ToString());
                        info.DangQianCangKuBianMa = Convert.ToInt32(dr["当前仓库编码"].ToString());
                        info.DangQianCangKu = dr["当前仓库"].ToString();
                        info.DangQianCunFangDian = dr["当前存放点"].ToString();
                        info.DangQianChePaiHaoMa = dr["当前车牌号码"].ToString();
                        info.ZiChanGuanLiBuMen = dr["资产管理部门"].ToString();
                        info.ZiChanGuanLiRen = dr["资产管理人"].ToString();
                        info.BeiZhu = dr["备注"].ToString();
                        info.ShiFouBaoFei = Convert.ToBoolean(dr["是否报废"]);

                        list.Add(info);
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogUtility.Error("FixedAssetsDictionaryDAL/GetAssetListNotScrapByType", ex.Message);
                return null;
            }
        }
    }
}
